﻿using System;
using System.ComponentModel;
using System.Data;
using System.Data.Common;
using Microsoft.Data.Tools.Schema.Sql.UnitTesting;
using Microsoft.Data.Tools.Schema.Sql.UnitTesting.Conditions;
using System.Text.RegularExpressions;

namespace SSDTConditions
{
    [ExportTestCondition("DateTime Value", typeof(DateTimeValueCondition))]
    public class DateTimeValueCondition : TestCondition
    {
        public enum CustomDateTimeFormat
        {
            System,
            MDY,
            DMY,
            YMD
        }
        public enum DateTimeValueVerificationCode 
        { 
            Ok,                 // condition is true
            NullExptected,      // get not null when null is expected
            FalseCondition,     // condition is false
            WrongActualValue,   // actual value is not a valid DateTime
            WrongExpectedValue  // expected value is not a valid DateTime
        };

        private int _resultSet;
        private int _column;
        private int _rowNumber;
        private string _expectedValue;
        private DateTime _expectedDateTime;
        private CustomDateTimeFormat _expectedValueFormat;
        private bool _nullExpected;
        private int _batch;

        public DateTimeValueCondition()
        {
            _resultSet = 1;
            _column = 1;
            _rowNumber = 1;
            _expectedValue = "";
            _nullExpected = false;
            _batch = 1;
        }

        // method you need to override to perform the condition verification
        public override void Assert(DbConnection validationConnection, SqlExecutionResult[] results)
        {
            // call base for parameter validation
            base.Assert(validationConnection, results);

            // verify batch exists
            if (results.Length < _batch)
            {
                throw new DataException(String.Format("Batch {0} does not exist", _batch));
            }

            SqlExecutionResult result = results[_batch - 1];

            // verify resultset exists
            if (result.DataSet.Tables.Count < ResultSet)
            {
                throw new DataException(String.Format("ResultSet {0} does not exist", ResultSet));
            }

            DataTable table = result.DataSet.Tables[ResultSet - 1];

            // actual condition verification
            DateTimeValueVerificationCode verificationResult = DateTimeConditionVerification(table);

            switch (verificationResult)
            {
                case DateTimeValueVerificationCode.NullExptected:
                    throw new DataException(String.Format(
                        "ResultSet {0} Row {1} Column {2} does not contain Null value expected", 
                        ResultSet, RowNumber, Column));
                case DateTimeValueVerificationCode.FalseCondition:
                    throw new DataException(String.Format(
                        "ResultSet {0} Row {1} Column {2} is not '{3}'", 
                        ResultSet, RowNumber, Column, ExpectedValue));
                case DateTimeValueVerificationCode.WrongActualValue:
                    throw new DataException("The Actual value is not a DateTime");
                case DateTimeValueVerificationCode.WrongExpectedValue:
                    throw new DataException("The Expected value is not a DateTime");
            }
        }

        public DateTimeValueVerificationCode DateTimeConditionVerification(DataTable table)
        {
            DateTimeValueVerificationCode returnCode = DateTimeValueVerificationCode.Ok;

            if (NullExpected)
            {
                if (table.Rows[RowNumber - 1][Column - 1] != DBNull.Value)
                {
                    returnCode = DateTimeValueVerificationCode.NullExptected;
                }
            }
            else
            {
                string formattedExpectedValue;
                if (!CustomDateTimeFormatter(ExpectedValue, ExpectedValueFormat, out formattedExpectedValue))
                {
                    // don't pass RegExp check
                    returnCode = DateTimeValueVerificationCode.WrongExpectedValue;
                }
                else
                {
                    DateTime actual, expected;
                    // check the type of the actual value is DateTime
                    if (!DateTime.TryParse(table.Rows[RowNumber - 1][Column - 1].ToString(), out actual))
                    {
                        returnCode = DateTimeValueVerificationCode.WrongActualValue;
                    }
                    else if (DateTime.TryParse(formattedExpectedValue, out expected))
                    {
                        if (actual == expected)
                        {
                            returnCode = DateTimeValueVerificationCode.Ok;
                        }
                        else
                        {
                            returnCode = DateTimeValueVerificationCode.FalseCondition;
                        }
                    }
                    else
                    {
                        // don't pass DateTime.TryParse check
                        returnCode = DateTimeValueVerificationCode.WrongExpectedValue;
                    }
                }
            }

            return returnCode;
        }

        /// <summary>
        /// Convert DateTime string to ISO8601 DateTime string yyyy-mm-dd hh:mi:ss.mmm
        /// </summary>
        /// <param name="value"></param>
        /// <param name="format"></param>
        /// <param name="result"></param>
        /// <returns></returns>
        public bool CustomDateTimeFormatter(string value, CustomDateTimeFormat format, out string result)
        {
            // format MDY: U.S. mm/dd/yyyy, USA mm-dd-yyyy
            // format DMY: British/French dd/mm/yyyy, German dd.mm.yyyy, Italian dd-mm-yyyy
            // format YMD: ANSI yyyy.mm.dd, Japan yyyy/mm/dd, ODBC yyyy-mm-dd, ISO yyyymmdd
            // time format: hh:mi:ss, hh:mi:ss:mmm, hh:mi:ss.mmm
            // and can be in 24-hour format or with AM/PM at the end

            // remove leading and trailing spaces
            value = value.Trim();

            if (format == CustomDateTimeFormat.System)
            {
                // let the .NET Framework to make a conversion
                DateTime convertedDateTime;
                bool conversionResult = DateTime.TryParse(value.Trim(), out convertedDateTime);
                result = convertedDateTime.ToString("s").Replace("T", " ");
                return conversionResult;
            }
            else
            {
                // check the Expected value for compliance the DateTime pattern
                Regex rxPattern = new Regex(@"(\d{1,2}[/.-]\d{1,2}[/.-]\d{4}|\d{4}[/.-]\d{1,2}[/.-]\d{1,2}|\d{8})([\sT]?\d{1,2}:\d{2}:\d{2}([:.]\d+)?(AM|PM)?)?", 
                    RegexOptions.None);
                Match match = rxPattern.Match(value);
                if (match.Success)
                {
                    // extract datetime with regexp pattern for subsequent operations
                    value = match.Value;
                }
                else
                {
                    result = "";
                    return false;
                }

                // find date and time separator, this can be a space or T
                int separatorIndex = value.ToUpper().IndexOfAny(new char[]{' ', 'T'});
                // divide string to date and time parts
                string dateString,
                    timeString = "";
                if (separatorIndex == -1)
                {
                    // there's no separator between date and time, so we consider it as a date only
                    dateString = value;
                }
                else
                {
                    dateString = value.Substring(0, separatorIndex);
                    timeString = value.Substring(separatorIndex + 1);
                }

                // convert date string to format yyyy-mm-dd
                dateString = dateString.Replace("/", "-").Replace(".", "-");
                string[] dateParts = dateString.Split('-');

                // check ISO format yyyymmdd
                if (format == CustomDateTimeFormat.YMD && dateParts.Length == 1)
                {
                    int dl = dateString.Length;
                    dateString = dateString.Substring(0, dl - 4) + "-"
                        + dateString.Substring(dl - 4, 2) + "-" + dateString.Substring(dl - 2, 2);
                }
                else
                {
                    switch (format)
                    {
                        case CustomDateTimeFormat.DMY:
                            dateString = dateParts[2] + "-" + dateParts[1].PadLeft(2, '0') 
                                + "-" + dateParts[0].PadLeft(2, '0');
                            break;
                        case CustomDateTimeFormat.MDY:
                            dateString = dateParts[2] + "-" + dateParts[0].PadLeft(2, '0') 
                                + "-" + dateParts[1].PadLeft(2, '0');
                            break;
                    }
                }

                result = dateString;

                // concatenate time string if needed
                if (!String.IsNullOrEmpty(timeString))
                {
                    // check P.M. in 12-hour format
                    if (timeString.ToUpper().EndsWith("PM"))
                    {
                        int hourSeparator = timeString.IndexOf(':');
                        int hour = int.Parse(timeString.Substring(0, hourSeparator));
                        if (timeString.ToUpper().EndsWith("PM"))
                        {
                            hour += 12;
                        }
                        timeString = hour.ToString() + timeString.Substring(hourSeparator).Replace("PM", "");
                    }
                    else
                    {
                        // remove AM at the end
                        timeString = timeString.Replace("AM", "");
                        // convert hour to 2-digit format
                        if (timeString.IndexOf(':') == 1)
                        {
                            timeString = "0" + timeString;
                        }
                    }

                    // replace millisecond separator from ':' to '.'
                    int msSeparator = timeString.IndexOf(':', 7);
                    if (msSeparator != -1)
                    {
                        timeString = timeString.Substring(0, msSeparator) + "."
                            + timeString.Substring(msSeparator + 1);
                    }
                    result += " " + timeString;
                }
            }

            return true;
        }

        // this method is called to provide the string shown in the
        // test conditions panel grid describing what the condition tests
        public override string ToString()
        {
            return String.Format(
                "Condition fails if ResultSet {0} Row {1} Column {2} is not '{4}'",
                ResultSet, RowNumber, Column, ExpectedValue);
        }

        // below are the test condition properties
        // that are exposed to the user in the property browser
        #region Properties

        // property specifying the resultset for which the test condition would be applied
        [Category("Test Condition")]
        [DisplayName("ResultSet")]
        [Description("ResultSet Number")]
        public int ResultSet
        {
            get { return _resultSet; }

            set
            {
                //basic validation
                if (value < 1)
                    throw new ArgumentException("ResultSet cannot be less than 1");

                _resultSet = value;
            }
        }

        // property specifying expected column count
        [Category("Test Condition")]
        [DisplayName("Column")]
        [Description("Column Number")]
        public int Column
        {
            get { return _column; }

            set
            {
                //basic validation
                if (value < 0)
                {
                    throw new ArgumentException("Column number cannot be less than 0");
                }

                _column = value;
            }
        }

        // property specifying expected row number count
        [Category("Test Condition")]
        [DisplayName("RowNumber")]
        [Description("Row Number")]
        public int RowNumber
        {
            get { return _rowNumber; }

            set
            {
                //basic validation
                if (value < 0)
                {
                    throw new ArgumentException("Row number cannot be less than 0");
                }

                _rowNumber = value;
            }
        }

        // property specifying expected string value
        [Category("Test Condition")]
        [DisplayName("ExpectedValue")]
        [Description("Expected Value")]
        public string ExpectedValue
        {
            get { return _expectedValue; }

            set
            {
                //basic validation
                if (String.IsNullOrEmpty(value))
                {
                    throw new ArgumentException("Expected value cannot be empty");
                }

                _expectedValue = value;
            }
        }

        // property specifying expected row number count
        [Category("Test Condition")]
        [DisplayName("NullExpected")]
        [Description("Null Expected")]
        public bool NullExpected
        {
            get { return _nullExpected; }

            set
            {
                _nullExpected = value;
            }
        }

        // property specifying expected row number count
        [Category("Test Condition")]
        [DisplayName("ExpectedValueFormat")]
        [Description("Expected Value DateTime Format. Can be derived from system locale or user-specified. Date separator can be a dot, dash or slash. Time can be in 12 or 24-hour format.")]
        public CustomDateTimeFormat ExpectedValueFormat
        {
            get { return _expectedValueFormat; }

            set
            {
                _expectedValueFormat = value;
            }
        }

        #endregion
    }
}
